﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook
{
    public class PolicyManager:ConnectionManager
    {
        #region Adding/Updating Policy
        public void AddPolicy(PolicyUnit oUnit)
        {
    
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "INSERT INTO OPOL (GroupCode, ObjType, PolicyGroupName, Status, DateCreated, CreatedBy, DateModified, ModifiedBy) VALUES (@GroupCode, @ObjType, @PolicyName, @Status, @DateCreated, @CreatedBy, @DateModified, @ModifiedBy)";
            oCommand.Parameters.Add(new SqlParameter("@GroupCode", oUnit.GroupCode));
            oCommand.Parameters.Add(new SqlParameter("@ObjType", oUnit.ObjectType));
            oCommand.Parameters.Add(new SqlParameter("@PolicyName", oUnit.PolicyName));
            oCommand.Parameters.Add(new SqlParameter("@Status", oUnit.DocumentStatus));
            oCommand.Parameters.Add(new SqlParameter("@DateCreated", oUnit.DateCreated));
            oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));
            oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));
            oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));

            oCommand.ExecuteNonQuery();

            foreach (DataRow oRow in oUnit.LineItems.Rows)
            {
                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "INSERT INTO POL1 (GroupCode, ObjType, ObjName, AccessType) VALUES (@GroupCode, @ObjType, @ObjName, @AccessType)";
                oCommand.Parameters.Add(new SqlParameter("@GroupCode", oUnit.GroupCode));
                oCommand.Parameters.Add(new SqlParameter("@ObjName", oRow["ObjName"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@ObjType", oRow["ObjType"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@AccessType", oRow["AccessType"].ToString()));
                

                oCommand.ExecuteNonQuery();
            }

        }

        public void UpdatePolicy(PolicyUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OPOL SET ObjType=@ObjType, PolicyGroupName=@PolicyName, Status=@Status, DateModified=@DateModified, ModifiedBy=@ModifiedBy WHERE GroupCode=@GroupCode";

            oCommand.Parameters.Add(new SqlParameter("@GroupCode", oUnit.GroupCode));
            oCommand.Parameters.Add(new SqlParameter("@ObjType", oUnit.ObjectType));
            oCommand.Parameters.Add(new SqlParameter("@PolicyName", oUnit.PolicyName));
            oCommand.Parameters.Add(new SqlParameter("@Status", oUnit.DocumentStatus));
            oCommand.Parameters.Add(new SqlParameter("@DateCreated", oUnit.DateCreated));
            oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));
            oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));
            oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));


            oCommand.ExecuteNonQuery();

            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "DELETE POL1 WHERE GroupCode = @GroupCode";
            oCommand.Parameters.Add(new SqlParameter("@GroupCode", oUnit.GroupCode));
            oCommand.ExecuteNonQuery();

            foreach (DataRow oRow in oUnit.LineItems.Rows)
            {
                if (oRow.RowState != DataRowState.Deleted)
                {
                    oCommand = new SqlCommand();
                    oCommand.Connection = this.Connection;
                    oCommand.CommandText = "INSERT INTO POL1 (GroupCode, ObjType, ObjName, AccessType) VALUES (@GroupCode, @ObjType, @ObjName, @AccessType)";
                    oCommand.Parameters.Add(new SqlParameter("@GroupCode", oUnit.GroupCode));
                    oCommand.Parameters.Add(new SqlParameter("@ObjType", oRow["ObjType"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ObjName", oRow["ObjName"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@AccessType",oRow["AccessType"].ToString()));
                    
                    oCommand.ExecuteNonQuery();
                }
            }
        }
        #endregion

        public DataTable GetPolicies()
        {
            DataTable dt = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OPOL";

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable GetPolicy(string _GroupCode)
        {
            DataTable dt = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OPOL WHERE GroupCode=@GroupCode";
            oCommand.Parameters.Add(new SqlParameter("@GroupCode", _GroupCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable GetPolicyLineItems(string _GroupCode)
        {
            DataTable dt = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT ObjType, ObjName, AccessType FROM POL1 WHERE GroupCode=@GroupCode";
            oCommand.Parameters.Add(new SqlParameter("@GroupCode", _GroupCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowsePolicyByGroupNumber(string _GROUP_NUMBER)
        {
            DataTable dt = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            
            _GROUP_NUMBER = '%' + _GROUP_NUMBER + '%';

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OPOL WHERE GroupCode LIKE @GroupCode";
            oCommand.Parameters.Add(new SqlParameter("@GroupCode", _GROUP_NUMBER));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }


        public DataTable BrowsePolicyByPolicyName(string _POLICY_NAME)
        {
            DataTable dt = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            _POLICY_NAME = '%' + _POLICY_NAME + '%';

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OPOL WHERE PolicyGroupName LIKE @PolicyGroupName";
            oCommand.Parameters.Add(new SqlParameter("@PolicyGroupName", _POLICY_NAME));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

    }
}
